package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import tools.DBTools;
import entity.Goods;
import entity.GoodsClass;
import entity.GoodsImg;
import entity.GoodsLabel;
import oracle.jdbc.internal.OracleCallableStatement;
import service.GoodsClassService;

public class GoodDetailDao {
	
	GoodsImgDao diDao = new GoodsImgDao();
	GoodsClassService gcService = new GoodsClassService();
	//获取一个商品商品根据Id
	public Goods getGoodByGid(int gid)
	{
		Goods good=null;
	    Connection conn = DBTools.getInstance().getConnection();
		String sql="select * from tbl_good_detail where good_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, gid);
			rs=ps.executeQuery();
			while(rs.next())
			{
				int goodId=rs.getInt(1);
				String goodName=rs.getString(2);
				double goodPrice=rs.getDouble(3);
				String goodInfo=rs.getString(4);
				int shopId=rs.getInt(5);
				int stock=rs.getInt(6);
				int goodClass=rs.getInt(7);
				good=new Goods(goodId, goodName, goodPrice, goodInfo, shopId, stock);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return good;
	}
	
	//添加一个商品
	public void addGoods(Goods goods, GoodsLabel label) {
		 Connection conn = DBTools.getInstance().getConnection();
		String sql = "insert into TBL_GOOD_DETAIL(GOOD_ID,GOOD_NAME,GOOD_PRICE,"
				+ "GOOD_INFO,SHOP_ID,STOCK,CLASS_ID,GOOD_DESC) values(sq_good_id.nextval,?,?,?,?,?,?,?)";
		String sql2 = "insert into TBL_GOOD_LABEL values(SQ_LABEL_ID.nextval, sq_good_id.currval, ?)";
		String sql3 = "insert into tbl_good_img(img_id,good_id,img_url) values(sq_img_id.nextval, sq_good_id.currval, '\\postImg\\592f76c613079.jpg')";
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		try {
			ps = conn.prepareStatement(sql);
			ps2 = conn.prepareStatement(sql2);
			ps3 = conn.prepareStatement(sql3);
					
			ps.setString(1, goods.getGoodName());
			ps.setDouble(2, goods.getGoodPrice());
			ps.setString(3, goods.getGoodInfo());
			ps.setInt(4, goods.getShopId());
			ps.setInt(5, goods.getStock());
			ps.setInt(6, goods.getGoodClass().getClassId());
			ps.setString(7, goods.getGoodDesc());
			
			ps2.setString(1, label.getLabelName());
			
			ps.executeUpdate();
			ps2.executeUpdate();
			ps3.executeUpdate();
		} catch (SQLException e) {
			System.out.println("商品添加异常");
			e.printStackTrace();
		} finally {
			if(ps!=null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
				}
			}
			if(ps2!=null) {
				try {
					ps2.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
				}
			}
			DBTools.getInstance().closeConnection(conn, ps3, null);
		}
	}
	
	//根据二级分类id和用户id获取商品
	public List<Goods> getGoodByTwoClassId(int userId, int twoClassId) {
		List<Goods> goodList = new ArrayList<>();
		 Connection conn = DBTools.getInstance().getConnection();
		String sql = "select * from TBL_GOOD_DETAIL where SHOP_ID = ? and CLASS_ID = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, twoClassId);
			rs = ps.executeQuery();
			while(rs.next()) {
				Goods goods = new Goods(rs.getInt("GOOD_ID"), 
						rs.getString("GOOD_NAME"));
				goodList.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return goodList;
	}
	//获取所有
	public List<Goods> getGoodsAll() {
		List<Goods> goodList = new ArrayList<>();
		 Connection conn = DBTools.getInstance().getConnection();
		String sql = "select * from tbl_good_detail";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				List<GoodsImg> goodImgList = diDao.getImgByGoodId(rs.getInt("GOOD_ID"));
				Goods goods = new Goods(rs.getInt("GOOD_ID"), rs.getString("GOOD_NAME"), 
						rs.getDouble("GOOD_PRICE"), rs.getString("GOOD_INFO"), goodImgList);
				goodList.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return goodList;
	}
	
	//查找商品根据近似度 
	public List<Goods> getGoodsByLike(String likeword) {
		List<Goods> goodList = new ArrayList<>();
		 Connection conn = DBTools.getInstance().getConnection();
		String sql = "select * from tbl_good_detail where GOOD_NAME like '%" + likeword
				+ "%' or good_name like '%" + likeword
				+ "' or good_name like '" + likeword + "%'";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				List<GoodsImg> goodImgList = diDao.getImgByGoodId(rs.getInt("GOOD_ID"));
				Goods goods = new Goods(rs.getInt("GOOD_ID"), rs.getString("GOOD_NAME"), 
						rs.getDouble("GOOD_PRICE"), rs.getString("GOOD_INFO"), goodImgList);
				goodList.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return goodList;
	}
	
	
	//分页操作，通过当前页数获取指定的商品
	public List<Goods> getGoodsByPage(int in1, int in2, String likeword) {
		List<Goods> goodList = new ArrayList<>();
		 Connection conn = DBTools.getInstance().getConnection();
		String sql = "{?=call func_pageGood(?,?,?)}";  //游标的sql语句
		CallableStatement callstate = null;     //使用CallableStatement封装
		ResultSet rs = null;
		try {
			callstate = conn.prepareCall(sql);
			callstate.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);  //设置第一个参数为游标类型，并且为输入属性
			callstate.setInt(2, in1); 
			callstate.setInt(3, in2);
			callstate.setString(4, likeword);
			callstate.execute();
			rs = (ResultSet) callstate.getObject(1);              //获取第一个位置的对象内容
			while(rs.next()) {
				List<GoodsImg> goodImgList = diDao.getImgByGoodId(rs.getInt("GOOD_ID"));
				Goods goods = new Goods(rs.getInt("GOOD_ID"), rs.getString("GOOD_NAME"),
						rs.getDouble("GOOD_PRICE"), rs.getString("GOOD_INFO"), goodImgList);
				goodList.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(callstate!=null) {
				try {
					callstate.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			DBTools.getInstance().closeConnection(conn, null, rs);
		}
		return goodList;
	}
	
	//获取商品详细信息根据Id
	public Goods getGoodByGoodDetail(int goodId) {
		Goods good = null;
		 Connection conn = DBTools.getInstance().getConnection();
		String sql = "select * from TBL_GOOD_DETAIL where GOOD_ID = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, goodId);
			rs = ps.executeQuery();
			while(rs.next()) {
				GoodsClass goodClass = gcService.getClassById(rs.getInt("CLASS_ID"));
				List<GoodsImg> goodImgList = diDao.getImgByGoodId(rs.getInt("GOOD_ID"));
				good = new Goods(rs.getInt("GOOD_ID"), rs.getString("GOOD_NAME"), rs.getDouble("GOOD_PRICE"),
						rs.getString("GOOD_INFO"), rs.getInt("SHOP_ID"), rs.getInt("STOCK"), goodClass,
						rs.getString("GOOD_STATUS"), rs.getString("GOOD_DESC"), new ArrayList<GoodsLabel>(), 
						goodImgList);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return good;
	}
	
	//获取一个商品商品根据Id有图片
	public Goods getGoodHaveImgByGid(int gid)
	{
		Goods good=null;
		 Connection conn = DBTools.getInstance().getConnection();
		String sql="select * from tbl_good_detail where good_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, gid);
			rs=ps.executeQuery();
			rs.next();
			List<GoodsImg> imgList = diDao.getImgByGoodId(rs.getInt("GOOD_ID"));
			int goodId=rs.getInt(1);
			String goodName=rs.getString(2);
			double goodPrice=rs.getDouble(3);
			String goodInfo=rs.getString(4);
			int shopId=rs.getInt(5);
			int stock=rs.getInt(6);
			good=new Goods(goodId, goodName, goodPrice, goodInfo, imgList);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return good;
	}
	
	//通过关键字获取有多少条记录数
	public int getCountByByLike(String likeword) {
		 Connection conn = DBTools.getInstance().getConnection();
			String sql = "select count(*) from tbl_good_detail where GOOD_NAME like '%" + likeword
					+ "%' or good_name like '%" + likeword
					+ "' or good_name like '" + likeword + "%'";
			PreparedStatement ps = null;
			ResultSet rs = null;
			int i=0;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				rs.next();
				i = rs.getInt(1);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DBTools.getInstance().closeConnection(conn, ps, rs);
			}
			return i;
	}
	
	//测试
	public static void main(String[] args) {
		GoodDetailDao gdd = new GoodDetailDao();
		List<Goods> goodsByPage = gdd.getGoodsByPage(1, 11, "苹果");
		for (Goods goods : goodsByPage) {
			System.out.println(goods.toString());
		}
	}
}
